package com.controller;

import java.io.BufferedInputStream;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Random;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.mysql.cj.jdbc.exceptions.SQLError;
import com.mysql.cj.util.StringUtils;

import user_information.Resume;
import user_information.User_now_in;
@Controller
public class JdbcController {
	@Autowired
	JdbcTemplate jdbcTemplate;
	//新增
	@GetMapping("/insert")
	public String insert(@RequestParam("username")String name,
			@RequestParam("password")String passwd)
	{
		if(StringUtils.isNullOrEmpty(passwd)||
				StringUtils.isNullOrEmpty(name))
		{
			return "error";
		}
		jdbcTemplate.execute("INSERT INTO `user`(`username`, `password`) VALUES ('"+
		name+"','"+passwd+"')");
		return "insertok";
	}
	@RequestMapping(value="/select",method = RequestMethod.GET)
	public String login(@RequestParam("username") String name,
			@RequestParam("password") String passwd,
			ModelMap map)
	{
		if(StringUtils.isNullOrEmpty(passwd)||
				StringUtils.isNullOrEmpty(name))
		{
			return "参数错误";
		}
		

		String sql = "SELECT username FROM `user` WHERE username='"+
		name+"' AND password='"+passwd+"'";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
		if(list.isEmpty())
		{
			return "error";
		}
		else {
			map.addAttribute("username", name);
			return "success";
		}
		
	}
	
	//插入简历内容
	
	public boolean insert_Resume(Resume new_resume,String username)
	{
		String sql = "INSERT INTO `resume`(`name`, `age`, `end_school`, `highest_school`, `working_age`, `whe_gratitued`, `username`)"+
	" VALUES ('"+new_resume.name()+"','"+new_resume.age()+"','"+new_resume.end_school()+"','"+new_resume.highest_school()+"','"+
				new_resume.working_age()+"','"+new_resume.whe_gratitued()+"','"+username+"')";
		try {
			jdbcTemplate.execute(sql);
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}
		return true;
	}
	//删除一个简历
	public boolean delete_Resume(Resume new_resume,String username) {
		return false;
		
	}
	
	//一个文件上传的内容，需要验证但大体没问题
    @RequestMapping(value = "/uploadFile", method = RequestMethod.POST)
    @ResponseBody
    public String updateFile(@RequestParam("file") MultipartFile file,
				@RequestParam("username") String u_name) {
        if (file.isEmpty()) {
            return "上传失败";
        }
        String fileName = file.getOriginalFilename();
        @SuppressWarnings("null")
		String suffixName = fileName.substring(fileName.lastIndexOf("."));
		
        //生成文件名称通用方法
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd_HHmmss");
        Random r = new Random();
        StringBuilder tempName = new StringBuilder();
        tempName.append(sdf.format(new Date())).append(r.nextInt(100)).append(suffixName);
        String newFileName = tempName.toString();
		File new_file = null;
		Path path = Paths.get(FILE_UP_LOAD_PATH + newFileName);
		byte[] bytes = null;
        try {
            // 保存文件
            
            bytes = file.getBytes();
            Files.write(path, bytes);
			
			
        } catch (IOException e) {
            e.printStackTrace();
        }
        File input_file = new File(path.toString());
        BufferedInputStream input = null;
        try {
			 input = new BufferedInputStream(new FileInputStream(
			        input_file
			    ));
		} catch (FileNotFoundException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		int length1 = bytes.length;
		ByteArrayInputStream array_bytes = new ByteArrayInputStream(bytes, 0, length1);
		BufferedInputStream to_insert = new BufferedInputStream(array_bytes); 
		String sql = "INSERT INTO `upload_file`(`file`, `file_id`, `file_type`, `user_name`,`file_name`) VALUES (?,NULL,?,?,'"+
		fileName+"')";
        new_file = new File(path.toString());
		try{
			int update = jdbcTemplate.update(sql,input,suffixName,u_name);
		System.out.println(update);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
        return "上传成功，地址为："+FILE_UP_LOAD_PATH + newFileName;
    }
	static final String FILE_UP_LOAD_PATH="upload\\";

    //多文件上传数据库操作，需要考虑负载问题，文件上传完整性
	public String uploadfiles(@RequestParam("file") MultipartFile[] files,
			@RequestParam("username") String u_name)
	{
		String uploadResult = "上传成功，地址为：<br>";
		for (MultipartFile file : files) {
			if (file.isEmpty()) {
	            return "上传失败";
	        }
	        String fileName = file.getOriginalFilename();
	        @SuppressWarnings("null")
			String suffixName = fileName.substring(fileName.lastIndexOf("."));
			
	        //生成文件名称通用方法
	        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd_HHmmss");
	        Random r = new Random();
	        StringBuilder tempName = new StringBuilder();
	        tempName.append(sdf.format(new Date())).append(r.nextInt(100)).append(suffixName);
	        String newFileName = tempName.toString();
			File new_file = null;
			Path path = Paths.get(FILE_UP_LOAD_PATH + newFileName);
			byte[] bytes = null;
	        try {
	            // 保存文件
	            
	            bytes = file.getBytes();
	            Files.write(path, bytes);
	            uploadResult += "/upload/" + newFileName + "<br>";
				
	        } catch (IOException e) {
	            e.printStackTrace();
	        }
	        File input_file = new File(path.toString());
	        BufferedInputStream input = null;
	        try {
				 input = new BufferedInputStream(new FileInputStream(
				        input_file
				    ));
			} catch (FileNotFoundException e) {
				// TODO 自动生成的 catch 块
				e.printStackTrace();
			}
			int length1 = bytes.length;
			ByteArrayInputStream array_bytes = new ByteArrayInputStream(bytes, 0, length1);
			BufferedInputStream to_insert = new BufferedInputStream(array_bytes); 
			String sql = "INSERT INTO `upload_file`(`file`, `file_id`, `file_type`, `user_name`,`file_name`) VALUES (?,NULL,?,?,'"+
			fileName+"')";
	        new_file = new File(path.toString());
			try{
				int update = jdbcTemplate.update(sql,input,suffixName,u_name);
			System.out.println(update);
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
			
	        
			
		}
		
		return uploadResult;
		
	}
	//插入简历数据库，包括各种内容
	public String insert_() {
		return null;
	}
	//删除路径path上的文件
	public boolean delete_file (String sPath) {  
	   boolean flag = false;  
	   File file = new File(sPath);  
	    // 路径为文件且不为空则进行删除  
	    if (file.isFile() && file.exists()) {  
	        file.delete();  
	        flag = true;  
	    }  
	    return flag;  
	}
}
